package com.example.datadictionary.service;

import com.example.datadictionary.model.TableInfo;
import com.example.datadictionary.model.ColumnInfo;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.util.List;

@Service
public class ExcelService {

    @Autowired
    private DatabaseService databaseService;

    public Workbook generateExcel() throws IOException {
        Workbook workbook = new XSSFWorkbook();
        // 创建目录Sheet
        Sheet tableSheet = workbook.createSheet("目录");
        List<TableInfo> tableInfos = databaseService.getTableInfo().stream().filter((tableInfo)->!tableInfo.getTableName().contains("copy")).toList();

        // 设置目录Sheet列宽
        tableSheet.setColumnWidth(0, 5 * 256); // 序号列宽度
        tableSheet.setColumnWidth(1, 30 * 256); // 表名称列宽度
        tableSheet.setColumnWidth(2, 40 * 256); // 表描述列宽度
        tableSheet.setColumnWidth(3, 15 * 256); // 备注列宽度
        tableSheet.setColumnWidth(4, 15 * 256); // 链接列宽度

        // 创建标题行并设置样式
        Row tableHeaderRow = tableSheet.createRow(0);
        createHeaderCell(workbook, tableHeaderRow, 0, "序号");
        createHeaderCell(workbook, tableHeaderRow, 1, "表名称");
        createHeaderCell(workbook, tableHeaderRow, 2, "表描述");
        createHeaderCell(workbook, tableHeaderRow, 3, "备注");
        createHeaderCell(workbook, tableHeaderRow, 4, "链接");

        // 写入表信息
        int rowNum = 1;
        for (TableInfo tableInfo : tableInfos) {
            Row row = tableSheet.createRow(rowNum++);
            row.createCell(0).setCellValue(rowNum - 1);
            row.createCell(1).setCellValue(tableInfo.getTableName());
            row.createCell(2).setCellValue(tableInfo.getTableComment());
            row.createCell(3).setCellValue(""); // 备注列内容为空

            // 创建超链接
            Cell linkCell = row.createCell(4);
            linkCell.setCellValue("-->");
            CreationHelper createHelper = workbook.getCreationHelper();
            XSSFHyperlink hyperlink = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.DOCUMENT);
            hyperlink.setAddress("'" + tableInfo.getTableName() + "'!A1");
            linkCell.setHyperlink(hyperlink);
            CellStyle linkStyle = workbook.createCellStyle();
            Font linkFont = workbook.createFont();
            linkFont.setUnderline(Font.U_SINGLE);
            linkFont.setColor(IndexedColors.BLUE.getIndex());
            linkStyle.setFont(linkFont);
            linkCell.setCellStyle(linkStyle);
        }

        // 添加目录Sheet框线
        addBordersToSheet(tableSheet, rowNum, 5);

        // 写入字段信息
        // 在写入字段信息的循环中，添加返回目录的单元格和超链接
        for (TableInfo tableInfo : tableInfos) {
            Sheet sheet = workbook.createSheet(tableInfo.getTableName());
            List<ColumnInfo> columnInfos = databaseService.getColumnInfo(tableInfo.getTableName());

            // 设置列宽
            sheet.setColumnWidth(0, 5 * 256); // 序号列宽度
            sheet.setColumnWidth(1, 30 * 256); // 字段名列宽度
            sheet.setColumnWidth(2, 15 * 256); // 字段类型列宽度
            sheet.setColumnWidth(3, 10 * 256); // 是否为空列宽度
            sheet.setColumnWidth(4, 20 * 256); // 默认值列宽度
            sheet.setColumnWidth(5, 40 * 256); // 字段备注列宽度
            sheet.setColumnWidth(6, 15 * 256); // 返回目录列宽度

            // 添加表名和表描述行
            Row tableNameRow = sheet.createRow(0);
            Row tableDescriptionRow = sheet.createRow(1);
            createMergedCell(workbook, tableNameRow, 0, 3, "表名称："+tableInfo.getTableName());
            createMergedCell(workbook, tableDescriptionRow, 0, 3, "表描述："+tableInfo.getTableComment());

            // 创建标题行
            int colRowNum = 2;
            Row headerRow = sheet.createRow(colRowNum++);
            createHeaderCell(workbook, headerRow, 0, "序号");
            createHeaderCell(workbook, headerRow, 1, "字段名");
            createHeaderCell(workbook, headerRow, 2, "字段类型");
            createHeaderCell(workbook, headerRow, 3, "是否为空");
            createHeaderCell(workbook, headerRow, 4, "默认值");
            createHeaderCell(workbook, headerRow, 5, "字段备注");
            createHeaderCell(workbook, headerRow, 6, "返回目录");

            // 添加返回目录超链接
            Cell returnLinkCell = headerRow.createCell(6);
            returnLinkCell.setCellValue("返回目录");
            CreationHelper createHelper = workbook.getCreationHelper();
            XSSFHyperlink returnHyperlink = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.DOCUMENT);
            returnHyperlink.setAddress("'目录'!A1");
            returnLinkCell.setHyperlink(returnHyperlink);
            CellStyle linkStyle = workbook.createCellStyle();
            Font linkFont = workbook.createFont();
            linkFont.setUnderline(Font.U_SINGLE);
            linkFont.setColor(IndexedColors.BLUE.getIndex());
            linkStyle.setFont(linkFont);
            returnLinkCell.setCellStyle(linkStyle);

            int serialNum = 1;
            for (ColumnInfo columnInfo : columnInfos) {
                Row row = sheet.createRow(colRowNum++);
                row.createCell(0).setCellValue(serialNum++);
                row.createCell(1).setCellValue(columnInfo.getColumnName());
                row.createCell(2).setCellValue(columnInfo.getColumnType());
                row.createCell(3).setCellValue(columnInfo.getIsNullable());
                row.createCell(4).setCellValue(columnInfo.getDefaultValue());
                row.createCell(5).setCellValue(columnInfo.getColumnComment());
            }

            // 添加字段信息Sheet框线
            addBordersToSheet(sheet, colRowNum, 6);
        }

        return workbook;
    }

    private void createHeaderCell(Workbook workbook, Row row, int column, String value) {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        cell.setCellStyle(style);
    }

    private void createMergedCell(Workbook workbook, Row row, int startColumn, int endColumn, String value) {
        Cell cell = row.createCell(startColumn);
        cell.setCellValue(value);
        CellStyle style = workbook.createCellStyle();
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        cell.setCellStyle(style);
        row.getSheet().addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), startColumn, endColumn));
    }

    private void addBordersToSheet(Sheet sheet, int rowCount, int colCount) {
        for (int i = 0; i < rowCount; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                row = sheet.createRow(i);
            }
            for (int j = 0; j < colCount; j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                }
                CellStyle style = sheet.getWorkbook().createCellStyle();
                if (cell.getCellStyle() != null) {
                    style.cloneStyleFrom(cell.getCellStyle());
                }
                style.setBorderTop(BorderStyle.THIN);
                style.setBorderBottom(BorderStyle.THIN);
                style.setBorderLeft(BorderStyle.THIN);
                style.setBorderRight(BorderStyle.THIN);
                cell.setCellStyle(style);
            }
        }
    }
}